package com.azt.provider.impl;

import com.azt.api.dto.CellStat;
import com.azt.api.dto.PurchaseModel;
import com.azt.api.pojo.ProRepositoryTitlesExcept;
import com.azt.api.service.ProRepositoryService;
import com.azt.api.service.ProRepositoryTitleService;
import com.azt.provider.model.ColumnWrap;
import com.azt.provider.model.ExcelWrap;
import com.azt.provider.model.TitleWrap;
import com.azt.utils.*;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jeecgframework.poi.util.PoiCellUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.io.IOException;
import java.io.InputStream;
import java.io.Serializable;
import java.net.URL;
import java.util.*;

/**
 * 解析excel
 *
 * @author 张栋 2016年9月4日下午8:26:11
 */
@Service
public class ParseExcel implements Serializable {

    @Autowired
    private ProRepositoryService proRepositoryService;

    @Autowired
    private ProRepositoryTitleService repositoryService;


    public PurchaseModel tryPurchase(String path, Integer adminid, Integer type) throws Exception {
        //所有的表数据
        ExcelWrap excelWrap = parseExcel(path, adminid, type);
        return capable(excelWrap, adminid, type);
    }


    /**
     * 分析一行的数据结构
     * Created by 张栋 on 2017/3/9 13:50
     */
    private CellStat getCellStat2(Workbook wb, Sheet sheet, Row row, int rownum) {
        row = sheet.getRow(rownum);
        //遇到行合并时, columns里会存在相同值
        List<String> columns = new ArrayList<String>();
        //遇到行合并时, nomerge_columns将只保存第一列,其余合并的值将不会保存
        List<String> nomerge_columns = new ArrayList<String>();
        //某一列是数值时存列序号,有行合并时,只取第一条
        List<Integer> hasnum = new ArrayList<Integer>();
        //记录行的特性,合并列也会重复记录, 普通列为1,单列向合并为1,行向合并为合并尺寸,双向合并取行向尺寸
        List<Integer> cellmap = new ArrayList<Integer>();
        //某一列包含数字的,存序号,有行合时,只取第一条
        List<Integer> containum = new ArrayList<Integer>();
        // 存储合并起始位置，合并方向,合并值,合并大小 .  且一个合并,只会保存一次
        List<String[]> mergedstat = new ArrayList<String[]>();
        int cellnum = 0;
        if (row != null) {
            short lastCellNum = row.getLastCellNum();
            int rowmergedsize = 0; // 行向合并尺寸
            while (cellnum < lastCellNum) {
                Cell cell = row.getCell(cellnum);
                if (cell == null) {
                    // 取到时为null,对应列值存空
                    cellnum++;
                    nomerge_columns.add("");
                    columns.add("");
                    cellmap.add(1);
                    continue;
                }

                //返回 1 是合并的行   2是合并的列 cell =cell  ,3双向合并
                int mergedFrom = PoiCellUtil.mergedFrom(sheet, rownum, cellnum);
                if (mergedFrom != 0) {//有合并
                    // 合并的值
                    String cellValue = PoiCellUtil.getMergedRegionValue(sheet, rownum, cellnum);

                    // 合并尺寸,为防止记录重复的合并
                    int mergedSize = PoiCellUtil.mergedSize(sheet, rownum, cellnum);

                    //正常excel的值, 当有合并时,  合并的第一列取到有值, 其余列取到的将为空
                    String cellv = PoiCellUtil.getCellValue(cell);

                    if (mergedFrom == 1) {
                        //行合并时,根据合并的尺寸来保证 nomerge_columns只保存一次值
                        if (rowmergedsize == 0) {
                            //仅合并第一列保存 ,momerge, merestat, hasnum , containnum
                            nomerge_columns.add(cellv);
                            //合并起始位置, 合并方向, 合并值, 合并大小
                            saveMergeStat(mergedstat, cellnum, mergedFrom, cellValue, mergedSize);
                            //保存数量相关
                            saveNumStat(hasnum, containum, cellnum, cellValue);
                            //记录合并大小, 下面的合并将不被保存
                            rowmergedsize = mergedSize;
                        } else {
                            rowmergedsize--;
                        }

                        cellmap.add(mergedSize);
                        columns.add(cellValue);
                    } else if (mergedFrom == 2) {
                        //列合并
                        nomerge_columns.add(cellValue);
                        saveMergeStat(mergedstat, cellnum, mergedFrom, cellValue, mergedSize);
                        saveNumStat(hasnum, containum, cellnum, cellValue);
                        // 列合并时为1
                        cellmap.add(1);
                        columns.add(cellValue);

                    } else if (mergedFrom == 3) {
                        // 有双向合并时
                        // 先拿取和并的列数
                        if (rowmergedsize == 0) {
                            nomerge_columns.add(cellValue);
                            saveMergeStat(mergedstat, cellnum, mergedFrom, cellValue, mergedSize);
                            saveNumStat(hasnum, containum, cellnum, cellValue);
                            rowmergedsize = mergedSize;
                        } else {
                            rowmergedsize--;
                        }
                        cellmap.add(mergedSize);
                        columns.add(cellValue);
                    }

                } else {
                    //没有合并是,正常取值与判断
                    String cellValue = PoiCellUtil.getCellValue(cell, null);
                    nomerge_columns.add(cellValue);
                    saveNumStat(hasnum, containum, cellnum, cellValue);
                    cellmap.add(1);
                    columns.add(cellValue);
                }

                cellnum++;
            }
        }

        CellStat stat = new CellStat();
        stat.setMergedstat(mergedstat);
        stat.setColumns(columns);
        stat.setSize(columns.size());
        stat.setCellSize(cellnum);
        stat.setHasnum(hasnum);
        stat.setContainum(containum);
//        stat.sethasindex(hasindex);
        stat.setNomergecolumns(nomerge_columns);
        stat.setCellmap(cellmap);
        stat.setRownum(rownum);
        return stat;
    }

    /**
     * 保存数量相关状态
     * Created by 张栋 on 2017/3/9 13:47
     */
    private void saveNumStat(List<Integer> hasnum, List<Integer> containum, int cellnum, String cellValue) {
        if (PatternUtils.isDigit(cellValue)) {
            hasnum.add(cellnum);
        }
        if (PatternUtils.hasDigit(cellValue)) {
            containum.add(cellnum);
        }
    }

    /**
     * 保存合并状态
     * Created by 张栋 on 2017/3/9 13:46
     */
    private void saveMergeStat(List<String[]> mergedstat, int cellnum, int mergedFrom, String cellValue, int mergedSize) {
        String[] meobj = new String[]{cellnum + "", mergedFrom + "", cellValue, mergedSize + ""};
        mergedstat.add(meobj);
    }


    /**
     * 优化结果
     * Created by 张栋 on 2017/3/9 15:18
     */
    private PurchaseModel capable(ExcelWrap excel, Integer adminid, Integer type) {
        SessionUtils.broadcast(adminid, "正在优化数据");
        //存需要删除的列的序号
        Set<Integer> needDeleteIndex = new HashSet<>();

        TitleWrap titleWrap = excel.titleWrap;

        //标题列
        List<String> titles = titleWrap.getColumns();
        //标题类型列
        List<Integer> allStat = titleWrap.allStat;

        //清掉标题列后面的连续的空列
        clenBackBlank(titles, 0);

        // 记录最长的列长
        Integer maxcellnum = titles.size();

        // 匹配出的内容项
        List<List<String>> finalcontent = new ArrayList<>();
        List<CellStat> cellStats = new ArrayList<>();
        List<ColumnWrap> columnWraps = excel.columnWraps;
        //取出匹配的结果
        List<String> catids = new ArrayList<>();
        //设备名称对应的md5Secret
        List<String> md5secret = new ArrayList<>();
        for (ColumnWrap columnWrap : columnWraps) {
            List<String> list = columnWrap.getColumns();
            clenBackBlank(list, maxcellnum);
            finalcontent.add(list);
            cellStats.add(columnWrap.cellStat);

            if (columnWrap.catids == null) {
                columnWrap.catids = "";
            }
            catids.add(columnWrap.catids);
            md5secret.add(columnWrap.md5Secret == null ? "" : columnWrap.md5Secret);
            if (maxcellnum < list.size()) {
                maxcellnum = list.size();
            }
        }

        //根据最大长度,不足最大长度的,补空
        repairList(titles, maxcellnum);
        for (List<String> list : finalcontent) {
            repairList(list, maxcellnum);
        }
        //补足
        for (int i = 0; i < maxcellnum - allStat.size(); i++) {
            allStat.add(0);
        }


        //删除序号列与序号之前的列
        if (titleWrap.hasIndex != -1) {
            for (int i = 0; i < titleWrap.hasIndex; i++) {
                needDeleteIndex.add(i);
            }
        }

        needDeleteIndex.addAll(titleWrap.exceptStatIndex);

        maxcellnum = maxcellnum - needDeleteIndex.size();
        //删除需要删除的列,包括标题和内容
        removeExcept(titles, needDeleteIndex);

        //同时删除标题匹配列
        removeExcept(allStat, needDeleteIndex);


        //是否有项编码
        boolean hasProjectNum = titleWrap.hasProjectNum != -1;
        //设备名称
        Integer productIndex = titleWrap.getProductNameIndex();
        //技术参数
        Integer descIndex = titleWrap.getDescIndex();

        //当有项目编码的时候,需要特殊处理,要将设备名称特殊处理,
        if (hasProjectNum && descIndex == -1 && productIndex != -1) {
            titles.add("技术参数");
            maxcellnum += 1;
            allStat.add(6);
        }

        //匹配到的内容
        Integer prefectContent = 0;
        int i = 0;
        for (List<String> list : finalcontent) {
            removeExcept(list, needDeleteIndex);
            if (hasProjectNum && productIndex != -1) {
                //如果有项止编码,且有设备名称列,则需要处理content
                String content = list.get(productIndex);
                int indexof = content.indexOf("\r\n");
                if (indexof != -1) {
                    String newcontent = content.substring(0, indexof);
                    String lastcontent = content.substring(indexof);
                    list.set(productIndex, newcontent);
                    if (descIndex != -1) {
                        list.set(descIndex, lastcontent);
                    } else {
                        list.add(lastcontent);
                    }
                }
            }

            //匹配的结果添加到集合中的第一列
            String ids = catids.get(i);
            if (!ids.equals("")) {
                prefectContent++;
            }
            list.add(0, catids.get(i));
            i++;
        }


        //处理下未匹配的行
        List<List<String>> nocontents = new ArrayList<>();
        List<CellStat> noCellStats = new ArrayList<>();
        List<CellStat> exceptcolumns = excel.exceptColumns;
        for (int y = 0; y < exceptcolumns.size(); y++) {
            CellStat stat = exceptcolumns.get(y);
            List<String> columns = stat.getNomergecolumns();
            boolean getnocontent = true;
            if (columns.size() == 0) {
                getnocontent = false;
            }
            for (int i1 = columns.size() - 1; i1 >= 0; i1--) {
                String noc = columns.get(i1);
                if (StringUtils.isBlank(noc)) {
                    columns.remove(i1);
                    if (i1 == 0) {
                        getnocontent = false;
                    }
                } else {
                    break;
                }
            }
            if (getnocontent) {
                columns.add(0, (stat.getRownum() + 1) + "");
                nocontents.add(columns);
                noCellStats.add(stat);
            }
        }


        //封装结果
        PurchaseModel model = new PurchaseModel();
        model.setContents(finalcontent);
        model.setTitleindex(allStat);
        model.setTitles(titles);
        model.setMaxlength(maxcellnum);
        model.setTitlePerfect(titleWrap.perfectNum == titles.size());
        model.setContentPerfect(md5secret.size() == prefectContent);
        model.setNocontents(nocontents);
        model.setShebeiscret(md5secret);
        model.setShebeiindex(titleWrap.getProductNameIndex());
        model.setCellStats(cellStats);
        model.setNoCellStats(noCellStats);
        SessionUtils.broadcast(adminid, "分析完成");
        return model;
    }

    private void removeExcept(Collection<?> list, Collection<Integer> except) {
        if (except == null || except.isEmpty()) return;
        Iterator<?> iterator = list.iterator();
        Integer i = 0;
        while (iterator.hasNext()) {
            iterator.next();
            if (except.contains(i)) {
                iterator.remove();
            }
            i++;
        }
    }

    /**
     * 修复list
     * 当不足maxcellnum 时,补空
     * Created by 张栋 on 2017/3/9 17:02
     */
    private void repairList(List<String> list, Integer maxcellnum) {
        if (list.size() < maxcellnum) {
            for (int i = 0; i < maxcellnum - list.size(); i++) {
                list.add("");
            }
        }
    }

    /**
     * 去掉集合后面的所有连续的空列
     * 且,列的数量保证不小于maxnum
     * Created by 张栋 on 2017/3/9 15:53
     */
    private List<String> clenBackBlank(List<String> contents, Integer minmum) {
        // 去掉连续的空列，从后开始
        for (int i = contents.size() - 1; i >= 0; i--) {
            String title = contents.get(i);
            if (i < minmum) {
                break;
            }
            if (StringUtils.isBlank(title)) {
                contents.remove(i);
            } else {
                break;
            }
        }
        return contents;
    }

    private ExcelWrap parseExcel(String path, Integer adminid, Integer type) throws Exception {
        List<TitleWrap> titleWraps = new ArrayList<>();//存可能的所有标题
        List<List<ColumnWrap>> columnWrapsList = new ArrayList<>();//每个标题下的内容
        List<ColumnWrap> tempColumns = new ArrayList<>();//临时存储用

        //可能无用的行
        List<CellStat> exceptColumns = new ArrayList<>();
        //可能的内容行
        List<ColumnWrap> mbColumns = new ArrayList<>();
        //最可能是标题
        TitleWrap mabTitle = null;


        /*
           row = sheet.getRow(rownum);
        * sheet.rowIterator()
        * 两个方法的区别,  iterator 方法,会过滤掉一些从未编辑的行(会比实际看到的行少)
        * gteRow()时, 取所有行
        *
        * */
        Workbook wb = toWorkbook(path);
        int sheetNum = wb.getNumberOfSheets();
        for (int i = 0; i < sheetNum; i++) {

            Sheet sheet = wb.getSheetAt(i);

            int lastRowNum = sheet.getLastRowNum();
            int rownum = 0;
            while (rownum <= lastRowNum) {
                SessionUtils.broadcast(adminid, "正在分析第" + (rownum + 1) + "行");
                CellStat cellStat = getCellStat2(wb, sheet, null, rownum);
                //判断是不是内容行
                ColumnWrap column = isColumn(cellStat, mbColumns, mabTitle);
                if (column.level > 0) {
                    tempColumns.add(column);
                    mbColumns.add(column);
                    //需要跳过的行
                    rownum += column.jumpSize;
                } else {
                    //判断是不是标题
                    TitleWrap title = isTitle(cellStat, type);
                    if (title.level > 0) {
//                         titleWraps.add(title);//记录每个可能的标题
//                        columnWrapsList.add(tempColumns);//截址该标题时的内容
//                        tempColumns.clear();//重新计数

                        // 选判断匹配等级,等级高优先,否则则取列数多的应该没错
                        if (mabTitle != null) {
                            if (mabTitle.level < title.level) {
                                mabTitle = title;
                            } else if (mabTitle.cellStat.getNomergecolumns().size() < title.cellStat.getNomergecolumns().size()) {
                                mabTitle = title;
                            }
                        } else {
                            mabTitle = title;
                        }
                    } else {
                        exceptColumns.add(cellStat);
                    }
                }
                rownum++;
            }
        }

        ExcelWrap excelWrap = new ExcelWrap();
        excelWrap.titleWrap = mabTitle;
        excelWrap.columnWraps = mbColumns;
        excelWrap.exceptColumns = exceptColumns;
        excelWrap.titleWraps = titleWraps;
        excelWrap.columnWrapsList = columnWrapsList;

        return excelWrap;
    }

    public List<PurchaseModel> tryPurchaseMulity(String path, Integer adminid, Integer type) throws Exception {
        List<ExcelWrap> excelWraps = parseExcelMulity(path, adminid, type);
        List<PurchaseModel> purchaseModels = new ArrayList<>();
        for (ExcelWrap excelWrap : excelWraps) {
            if (!excelWrap.columnWraps.isEmpty()) {
                PurchaseModel capable = capable(excelWrap, adminid, type);
                purchaseModels.add(capable);
            }
        }
        return purchaseModels;
    }

    private List<ExcelWrap> parseExcelMulity(String path, Integer adminid, Integer type) throws Exception {

        List<ExcelWrap> excelWraps = new ArrayList<>();

        //可能无用的行
        List<CellStat> exceptColumns = new ArrayList<>();
        //可能的内容行
        List<ColumnWrap> mbColumns = new ArrayList<>();
        //最可能是标题
        TitleWrap mabTitle = null;

        List<ColumnWrap> tempColumns = new ArrayList<>();//临时存储用
        boolean  titleagin=false;
        Workbook wb = toWorkbook(path);
        int sheetNum = wb.getNumberOfSheets();
        for (int i = 0; i < sheetNum; i++) {
            Sheet sheet = wb.getSheetAt(i);
            int lastRowNum = sheet.getLastRowNum();
            int rownum = 0;
            while (rownum <= lastRowNum) {
                SessionUtils.broadcast(adminid, "正在分析第" + (rownum + 1) + "行");
                CellStat cellStat = getCellStat2(wb, sheet, null, rownum);
                //判断是不是内容行
                ColumnWrap column = isColumn(cellStat, mbColumns, mabTitle);
                if (column.level > 0) {
                    titleagin=false;
                    tempColumns.add(column);
                    //需要跳过的行
                    rownum += column.jumpSize;
                } else {
                    //判断是不是标题
                    TitleWrap title = isTitle(cellStat, type);
                    if (title.level > 0) {

                        if (!tempColumns.isEmpty()) {
                            //再次发现一个标题时,
                            ExcelWrap excelWrap = getExcelWrap(exceptColumns, tempColumns,mabTitle);
                            excelWraps.add(excelWrap);
                            exceptColumns=new ArrayList<>();
                            tempColumns=new ArrayList<>();
                            mabTitle=null;
                        }

                        //连续的标题出现时,取最有可能的一个
                        if (titleagin && mabTitle != null) {
                            if (mabTitle.level < title.level) {
                                mabTitle = title;
                            } else if (mabTitle.cellStat.getNomergecolumns().size() < title.cellStat.getNomergecolumns().size()) {
                                mabTitle = title;
                            }
                        } else {
                            mabTitle = title;
                        }


                        titleagin=true;
                    } else {
                        exceptColumns.add(cellStat);
                    }
                }
                rownum++;
            }
        }

        ExcelWrap excelWrap = new ExcelWrap();
        excelWrap.columnWraps = tempColumns;
        excelWrap.exceptColumns = exceptColumns;
        excelWrap.titleWrap = mabTitle;

        excelWraps.add(excelWrap);
        return excelWraps;
    }

    private ExcelWrap getExcelWrap(List<CellStat> exceptColumns, List<ColumnWrap> mbColumns,TitleWrap titleWrap) throws IOException, ClassNotFoundException {
        ExcelWrap excelWrap = new ExcelWrap();
        excelWrap.columnWraps = mbColumns;
        excelWrap.exceptColumns = exceptColumns;
        excelWrap.titleWrap = titleWrap;
        return excelWrap;
    }


    /**
     * @param cellStat  不前列
     * @param precolumn 历史列
     * @param title     标题
     * @return
     */
    private ColumnWrap isColumn(CellStat cellStat, List<ColumnWrap> precolumn, TitleWrap title) {

        ColumnWrap columnWrap = new ColumnWrap();
        columnWrap.cellStat = cellStat;
        columnWrap.rownum = cellStat.getRownum();

        Integer level = 0;
        List<String> nomergecolumns = cellStat.getNomergecolumns();
        //去掉空列与数字列后要大于0, 总列数不小于3,且不为空的列至少要大于1吧
        if (cellStat.getNoBankAndNumber() > 0 && cellStat.getNoBankExceptSize(title == null ? null : title.allStat) > 1 && nomergecolumns.size() > 3) {
            //处理有特殊合并的情况
            if (cellStat.hasMerged()) {
                if (cellStat.getMergedstat().get(0)[1].equals("2")
                        && cellStat.getMergedstat().size() == cellStat.getCellnum()) {
                    // 列合并，而且每一列都是合并，合并尺寸也都相同， 这种说明表格都是合并过的,是不正常的合并，这种合并只取一次，剩下的跳过
                    String size = cellStat.getMergedstat().get(0)[3];
                    columnWrap.jumpSize = Integer.parseInt(size);
                }
            }

            if (title != null) {

                //看有没有匹配到数量列,以数量列为切入
                Integer quantityIndex = title.getQuantityIndex();
                if (quantityIndex != -1) {
                    //查看数量列是不是数字
                    String num = nomergecolumns.get(quantityIndex);
                    if (NumberValidationUtils.isRealNumber(num)) {
                        level += 30;
                    }
                }

                //设备名称能不能找的到
                Integer productName = title.getProductNameIndex();
                if (productName != -1) {
                    String pname = nomergecolumns.get(productName);
                    //含有项目编码的,要处理下设备名称
                    if (title.hasProjectNum != -1 && pname.indexOf("\r\n") != -1) {
                        pname = pname.substring(0, pname.indexOf("\r\n"));
                    }
                    String trim = MyStringUtils.replaceBlank(pname);
                    String md5_16 = PasswordUtil.MD5_16(trim);
                    String catids = proRepositoryService.getPcatidBymd5(md5_16);
                    if (catids != null) {
                        level += 30;
                    } else {
                        catids = "";
                    }

                    columnWrap.md5Secret = md5_16;
                    columnWrap.catids = catids;
                }
            }


            //与历史结构对比,有匹配的则有可能是
            if (!precolumn.isEmpty()) {
                String nowmap = cellStat.getCellMapString();
                for (int i = precolumn.size() - 1; i >= 0; i--) {
                    String premap = precolumn.get(i).cellStat.getCellMapString();
                    if (nowmap.equals(premap)) {
                        level += 30;
                        break;
                    }
                }
            }

            //如果含有数字,且最后一个数字的位置大于1,有可能是有效的行
            if (cellStat.getHasnum().size() >= 1 && cellStat.getLastNum() > 1) {
                level += 10;
            }
        }
        columnWrap.level = level;
        return columnWrap;
    }


    private TitleWrap isTitle(CellStat cellStat, Integer type) {

        TitleWrap titleWrap = new TitleWrap();
        titleWrap.cellStat = cellStat;
        titleWrap.rownum = cellStat.getRownum();
        titleWrap.hasProjectNum = 0;//是否含有项目编码

        Integer level = 0;//数值越大可能性越大
        //不为空的列数量,并且不含有只有数字的列
        if (cellStat.getNoBankContentSize() >= 3
                //没有数字
                && cellStat.getHasnum().size() == 0) {

            List<String> nomergecolumns = cellStat.getNomergecolumns();

            List<Integer> allStat = new ArrayList<>();
            List<Integer> exceptStatIndex = new ArrayList<>();
            Integer perfectnum = 0;
            for (int j = 0; j < nomergecolumns.size(); j++) {
                String title = nomergecolumns.get(j);
                //先简单的判断是不是有项目编码来分辨特殊需求的表
                if (MyStringUtils.replaceBlank(title).equals("项目编码")) {
                    titleWrap.hasProjectNum = j;
                }

                if (MyStringUtils.replaceBlank(title).equals("序号")) {
                    titleWrap.hasIndex = j;
                }


                Integer titleid = repositoryService.getTitleIdByTitle(title, type);
                if (titleid == 0) {//排除项检查
                    //检查排除
                    ProRepositoryTitlesExcept except = repositoryService.getExceptByTitle(title, type);
                    if (except != null) {
                        allStat.add(-1);
                        exceptStatIndex.add(j);
                    } else {
                        allStat.add(0);
                    }
                } else {
                    allStat.add(titleid);
                    perfectnum++;
                }

            }

            //有对应匹配上的标题,可能性大增,否则可能性一般
            titleWrap.perfectNum = perfectnum;
            if (titleWrap.perfectNum != 0) {
                level += 100;
            } else {
                level += 10;
            }

            titleWrap.exceptStatIndex = exceptStatIndex;
            titleWrap.allStat = allStat;
        }

        titleWrap.level = level;


        return titleWrap;
    }


    /**
     * 得到一个字符串的长度,显示的长度,一个汉字或日韩文长度为2,英文字符长度为1
     *
     * @return int 得到的字符串长度
     */
    public int length(String s) {
        if (s == null)
            return 0;
        char[] c = s.toCharArray();
        int len = 0;
        for (int i = 0; i < c.length; i++) {
            len++;
            if (!isLetter(c[i])) {
                len++;
            }
        }
        return len;
    }

    public boolean isLetter(char c) {
        int k = 0x80;
        return c / k == 0 ? true : false;
    }


    /**
     * 根据地址。转换成workbook
     *
     * @param path
     * @return
     * @throws Exception
     * @author 张栋 2016年5月19日下午2:26:02
     */
    private Workbook toWorkbook(String path) throws Exception {
        URL url = new URL(path);
        InputStream file = url.openStream();
        Workbook wb = null;
        try {
            // 2003
            wb = new HSSFWorkbook(file);
        } catch (Exception e) {
            file.close();
            // 2007
            try {
                file = url.openStream();
                wb = new XSSFWorkbook(file);
            } catch (Exception e1) {
                file.close();
                // 重新测试一次 ，更改后缀
                // 计划重新上传，并改后缀
                String key = OSSFileUtils.getKey(path, null);
                String[] split = StringUtils.split(key, ".");
                StringBuffer sb = new StringBuffer();
                sb.append(split[0]);
                sb.append("_c");
                if (split[1].equals("xls")) {
                    sb.append(".xlsx");
                } else {
                    sb.append(".xls");
                }

                String copy = OSSFileUtils.copy(key, sb.toString());
                url = new URL(copy);
                file = url.openStream();
                try {
                    wb = new HSSFWorkbook(file);
                } catch (Exception ex) {
                    wb = new XSSFWorkbook(file);
                }
            }
        }

        return wb;
    }


}
